package model
{
	import flash.data.SQLResult;
	import flash.data.SQLSchema;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	
	import mx.controls.Alert;
	import mx.managers.CursorManager;
	public class InventoryAdjustments extends Inventory
	{
		[Bindable] private var _dataProvider:Array;
		private var sql_statement:SQLStatement;
		private static const CREATE_PRODUCT_SUPPLIER:String =
			"CREATE TABLE IF NOT EXISTS adjustments (pk BIGINT PRIMARY KEY, product_pk BIGINT, qty INTEGER, date BIGINT, userid TEXT, desc TEXT)";
		private static const CREATE_INDEX_PRODUCT_SUPPLIER_1:String =
			"﻿CREATE INDEX IF NOT EXISTS index_adjustments_1 ON adjustments(product_pk ASC);";
		private static const CREATE_INDEX_PRODUCT_SUPPLIER_2:String =
			"﻿CREATE INDEX IF NOT EXISTS index_adjustments_2 ON adjustments(qty ASC);";
		private static const CREATE_INDEX_PRODUCT_SUPPLIER_3:String =
			"﻿CREATE INDEX IF NOT EXISTS index_adjustments_3 ON adjustments(date ASC);";
		private static const CREATE_INDEX_PRODUCT_SUPPLIER_4:String =
			"﻿CREATE INDEX IF NOT EXISTS index_adjustments_4 ON adjustments(userid ASC);";
		private static const CREATE_INDEX_PRODUCT_SUPPLIER_5:String =
			"﻿CREATE INDEX IF NOT EXISTS index_adjustments_5 ON adjustments(desc ASC);";
		public function InventoryAdjustments(s_product_pk:String = "")
		{
			//TODO: implement function
			super();
			func_create_table(CREATE_PRODUCT_SUPPLIER);
			for each (var s_create:String in [CREATE_INDEX_PRODUCT_SUPPLIER_1, CREATE_INDEX_PRODUCT_SUPPLIER_2, CREATE_INDEX_PRODUCT_SUPPLIER_3, CREATE_INDEX_PRODUCT_SUPPLIER_4, CREATE_INDEX_PRODUCT_SUPPLIER_5]) {
				func_create_index(s_create);
			}
			if (s_product_pk.length > 0) func_queryall(s_product_pk);
		}
		
		/**
		 * retrieve all product adjustments
		*/
		public function func_queryall(s_product_pk:String):void {
			CursorManager.setBusyCursor();
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.text = "SELECT pk, product_pk, qty, date, userid, desc FROM adjustments WHERE product_pk = :product_pk";
			sql_statement.parameters[":product_pk"] = s_product_pk;
			sql_statement.execute();
			func_populate_product_adjustments(sql_statement.getResult());
			CursorManager.removeBusyCursor();
		}
		
		/**
		 * retrieve some product adjustments
		*/
		public function func_querysome(args:Object):void {
			CursorManager.setBusyCursor();
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			var str_query:String = "SELECT pk, product_pk, qty, date, userid, desc FROM adjustments WHERE product_pk = :product_pk";
			sql_statement.parameters[":product_pk"] = args.product_pk;
			if (args.date_from != null) {
				str_query += " AND date >= :date_from"; 
				sql_statement.parameters[":date_from"] = args.date_from;
			}
			if (args.date_to != null) {
				str_query += " AND date <= :date_to";
				sql_statement.parameters[":date_to"] = args.date_to;
			}
			sql_statement.text = str_query;
			sql_statement.execute();
			func_populate_product_adjustments(sql_statement.getResult());
			CursorManager.removeBusyCursor();
		}
		
		/**
		 * retrieve qty as of date given
		*/
		public function func_qty_as_of(s_product_pk:String, s_date_to:String):Number {
			CursorManager.setBusyCursor();
			var sql_statement:SQLStatement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			var str_query:String = "SELECT SUM(qty) AS TOTAL FROM adjustments WHERE product_pk = :product_pk";
			sql_statement.parameters[":product_pk"] = s_product_pk;
			
			str_query += " AND date <= :date_to";
			sql_statement.parameters[":date_to"] = s_date_to;
			
			
			sql_statement.text = str_query;
			sql_statement.execute();
			CursorManager.removeBusyCursor();
			return Number(sql_statement.getResult().data[0].TOTAL);
		}
		
		/**
		 * populate results to an array
		*/
		private function func_populate_product_adjustments(result:SQLResult):void {
			dataProvider = result.data;
		}
		
		/**
		 * adds new product adjusment
		*/
		public function func_add_new(args:Object):void {
			sql_statement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.addEventListener(SQLErrorEvent.ERROR, hdlr_error_insertion);
			sql_statement.text = "INSERT INTO adjustments(pk, product_pk, qty, date, userid, desc) VALUES(:pk, :product_pk, :qty, :date, :userid, :desc)";
			sql_statement.parameters[":pk"] = args.pk;
			sql_statement.parameters[":product_pk"] = args.product_pk;
			sql_statement.parameters[":qty"] = args.qty;
			sql_statement.parameters[":date"] = args.date;
			sql_statement.parameters[":userid"] = args.userid;
			sql_statement.parameters[":desc"] = args.desc;
			sql_statement.execute();
		}
		
		/**
		 * adds new product adjusment
		*/
		public function func_delete(args:Object):void {
			sql_statement = new SQLStatement();
			sql_statement.sqlConnection = sql_conn;
			sql_statement.addEventListener(SQLErrorEvent.ERROR, hdlr_error_insertion);
			//if (args.qty > 0)
			sql_statement.text = "UPDATE products " + 
									"SET available_quantity = available_quantity - " + args.qty +
									" WHERE pk = :product_pk";
			//else
			//sql_statement.text = "UPDATE products " + 
			//						"SET available_quantity = available_quantity + " + args.qty +
			//						" WHERE pk = :product_pk";
			sql_statement.parameters[":product_pk"] = args.product_pk;
			sql_statement.execute();
			
			var sql_statement2:SQLStatement = new SQLStatement();
			sql_statement2.sqlConnection = sql_conn;
			sql_statement2.addEventListener(SQLErrorEvent.ERROR, hdlr_error_insertion);
			sql_statement2.text = "DELETE FROM adjustments WHERE pk = :pk";
			sql_statement2.parameters[":pk"] = args.pk;
			sql_statement2.execute();
		}
		
		/**
		 * handles data insertion error
		*/
		private function hdlr_error_insertion(evt:SQLErrorEvent):void {
			Alert.show(evt.target.toString());
		}
		
		/**
		 * set data provider
		*/
		public function set dataProvider(results:Array):void {
			_dataProvider = results;
		}
		
		/**
		 * get data provider
		*/
		public function get dataProvider():Array {
			return _dataProvider;
		}
		
		/**
		 * get qty on hand
		*/
		public function get total_adjustments():int {
			var i_ret:int = 0;
			CursorManager.setBusyCursor();
			if (dataProvider != null) {
				if (dataProvider.length > 0) {
					for (var itr:int = 0; itr < dataProvider.length; itr++) {
						i_ret += dataProvider[itr].qty;		
					}
				}
			}
			CursorManager.removeAllCursors();
			return i_ret;
		}
	}
}